package crud

import (
	"errors"
	"fmt"
	"regexp"
	"strings"

	"gitee.com/dayu1985/framework/logging"

	"github.com/chinahdkj/xorm"
	"github.com/go-xorm/builder"
)

//Condition 条件
type Condition struct {
	Field    string      `json:"Field"`    //查询字段
	Group    int         `json:"Group"`    //条件分组
	Relation string      `json:"Relation"` //关系 "and", "or"
	Operate  string      `json:"Operate"`  // 操作符 "like" , ">=" , "<=" , ">", "<", "nil" , "!=" , "in", "between"
	Value    interface{} `json:"Value"`    // 条件值
}

//Order 排序
type Order map[string]int

//Fields 字段
type Fields map[string]int

//CQuery 查询表达式
type CQuery struct {

	//公用查询
	Size       int         `json:"size"`
	Index      int         `json:"index"`
	Count      bool        `json:"count"`
	Conditions []Condition `json:"conditions"`
	Order      []Order     `json:"order"`
	Fields     []Fields    `json:"fields"`

	//非公用查询
	Params map[string]interface{} `json:"query"`
}

//NewQuery 新查询
func NewQuery() *CQuery {
	var m = CQuery{}
	m.Count = true
	m.Index = 1
	m.Conditions = make([]Condition, 0)
	m.Order = make([]Order, 0)
	m.Fields = make([]Fields, 0)
	return &m
}

//NewCondition 新查询条件
func (q *CQuery) NewCondition(Field string, Operate string, Value interface{}) *CQuery {
	c := Condition{Field: Field, Group: 0, Relation: "and", Operate: Operate, Value: Value}
	q.Conditions = append(q.Conditions, c)
	return q
}

//NewOrder 新排序条件
func (q *CQuery) NewOrder(field string, asc int) *CQuery {
	o := Order{field: asc}
	q.Order = append(q.Order, o)
	return q
}

func (m *CQuery) Alias(alias string) {
	m.AliasCondition(alias)
	m.AliasOrder(alias)
}

func (m *CQuery) AliasCondition(alias string) {
	newCondition := []Condition{}
	for _, ord := range m.Conditions {
		temp := Condition{}
		temp.Field = ord.Field
		temp.Group = ord.Group
		temp.Relation = ord.Relation
		temp.Operate = ord.Operate
		temp.Value = ord.Value
		if !strings.Contains(ord.Field, ".") {
			temp.Field = alias + "." + ord.Field
		}
		newCondition = append(newCondition, temp)
	}
	m.Conditions = newCondition
}

func (m *CQuery) AliasOrder(alias string) {
	newOrder := []Order{}
	for _, ord := range m.Order {
		torder := map[string]int{}
		for k, v := range ord {
			if strings.Contains(k, ".") {
				torder[k] = v
			} else {
				torder[alias+"."+k] = v
			}
		}
		newOrder = append(newOrder, torder)
	}
	m.Order = newOrder
}

//NewField 新查询字段
func (q *CQuery) NewField(f Fields) *CQuery {

	q.Fields = append(q.Fields, f)
	return q
}

//InjectionTestor 注入检测
var InjectionTestor = regexp.MustCompile(`(?:')|(?:--)|(/\\*(?:.|[\\n\\r])*?\\*/)|(\b(select|update|and|or|delete|insert|trancate|char|chr|into|substr|ascii|declare|exec|count|master|into|drop|execute)\b)`)

func handleConditions(session *xorm.Session, query *CQuery) error {

	for _, condition := range query.Conditions {

		if InjectionTestor.MatchString(condition.Field) {
			logging.Warn("发现SQL注入：%v", condition)
			return errors.New("发现SQL注入：" + fmt.Sprintf("%v", condition))
		}

		var conf interface{}
		if len(condition.Operate) == 0 {
			condition.Operate = "="
		}

		switch condition.Operate {
		case "like":
			//session.And(condition.Field+" LIKE ?", "%"+condition.Value.(string)+"%")
			conf = builder.Like{condition.Field, condition.Value.(string)}
		case "=":
			fs := builder.Eq{}
			fs[condition.Field] = condition.Value
			conf = fs
		case ">=":
			fs := builder.Gte{}
			fs[condition.Field] = condition.Value
			conf = fs
			//session.And(condition.Field+" >= ?", condition.Value)
		case ">":
			fs := builder.Gt{}
			fs[condition.Field] = condition.Value
			conf = fs
			//session.And(condition.Field+" > ?", condition.Value)
		case "<=":
			fs := builder.Lte{}
			fs[condition.Field] = condition.Value
			conf = fs
			//session.And(condition.Field+" <= ?", condition.Value)
		case "<":
			fs := builder.Lt{}
			fs[condition.Field] = condition.Value
			conf = fs
			//session.And(condition.Field+" < ?", condition.Value)
		case "nil":
			conf = builder.IsNull{condition.Field}
			//session.And(condition.Field + " IS NULL")
		case "nnil":
			conf = builder.NotNull{condition.Field}
			//session.And(condition.Field + " IS NULL")
		case "!=":
			conf = builder.Neq{condition.Field: condition.Value}
			//session.And(condition.Field+" <> ?", condition.Value)
		case "in":
			o := condition.Value.([]interface{})
			if len(o) > 0 {
				conf = builder.In(condition.Field, o)
				//session.In(condition.Field, o)
			} else {
				conf = builder.Eq{"ID": "NULL"}
				//session.And("1 = 0")
			}
		case "between":
			o := condition.Value.([]interface{})
			//session.And(condition.Field+" >= ? AND "+condition.Field+" <= ?", o[0], o[1])
			conf = builder.Between{Col: condition.Field, LessVal: o[0], MoreVal: o[1]}
		}

		wstr, args, err := builder.ToSQL(conf)

		if err != nil {
			logging.Warn("条件错误：%v", err)
			return errors.New("条件错误：" + fmt.Sprintf("%v", err))
		}

		session.Where(wstr, args...)
	}
	return nil
}

func (query *CQuery) ParseConditions() (map[string]interface{}, error) {
	var result map[string]interface{} = map[string]interface{}{}
	for _, condition := range query.Conditions {
		if InjectionTestor.MatchString(condition.Field) {
			logging.Warn("发现SQL注入：%v", condition)
			return nil, errors.New("发现SQL注入：" + fmt.Sprintf("%v", condition))
		}
		switch condition.Operate {
		case "=":
			result[condition.Field] = condition.Value
		case "nil":
			result[condition.Field] = nil
		case "in":
			o := condition.Value.([]interface{})
			if len(o) > 0 {
				result[condition.Field] = o
			} else {
				result[condition.Field] = nil
			}
		case "between":
			o := condition.Value.([]interface{})
			result[condition.Field] = o
		}
	}
	return result, nil
}

func handleFields(session *xorm.Session, query *CQuery) error {

	for _, field := range query.Fields {

		var fs []string
		var nfs []string

		for k, v := range field {

			if v > 0 {
				fs = append(fs, k)
			} else {
				nfs = append(nfs, k)
			}
		}

		if len(fs) > 0 {
			session.Cols(fs...)
		}

		if len(nfs) > 0 {
			session.Omit(nfs...)
		}
	}

	return nil
}

func handleOrders(session *xorm.Session, query *CQuery) error {

	for _, order := range query.Order {
		for name, i := range order {
			if i > 0 {
				session.Asc(name)
			} else {
				session.Desc(name)
			}
		}
	}

	return nil
}

func (q *CQuery) Items(session *xorm.Session) error {

	err := q.Filter(session)

	if err != nil {
		return err
	}

	return q.GetItems(session)
}

func (q *CQuery) GetItems(session *xorm.Session) error {

	err := handleOrders(session, q)

	if err != nil {
		return err
	}

	err = handleFields(session, q)

	if err != nil {
		return err
	}

	if q.Size > 0 {
		session.Limit(q.Size, (q.Index-1)*q.Size)
	}

	return nil
}

func (q *CQuery) Filter(session *xorm.Session) error {
	for k, v := range q.Params {
		q.NewCondition(k, "=", v)
	}
	return handleConditions(session, q)
}

func (q *CQuery) Query(sess *xorm.Session, bean interface{}, beans interface{}) (int64, error) {

	q.Filter(sess)
	count := int64(0)

	if q.Count {
		count, _ = sess.Clone().Count(bean)
	}

	err := q.GetItems(sess)

	if err != nil {
		return 0, err
	}

	err = sess.Find(beans)

	if err != nil {
		return 0, err
	}

	return count, nil
}

func (q *CQuery) SimpleQuery(sess *xorm.Session) (interface{}, error) {

	q.Filter(sess)
	err := q.GetItems(sess)
	if err != nil {
		return nil, err
	}
	rst, err := sess.Query().List()
	if err != nil {
		return nil, err
	}
	return rst, nil
}

func (q *CQuery) SqlQuery(sess *xorm.Session, sql string, bean interface{}, beans interface{}) (int64, error) {

	sess = sess.SQL(sql)

	q.Filter(sess)
	count := int64(0)

	if q.Count {
		count, _ = sess.Clone().Count(bean)
	}

	q.GetItems(sess)
	sess.Find(beans)

	return count, nil
}

func (query *CQuery) HandleSqlConditions(sql *string) (args []interface{}, err error) {

	for _, condition := range query.Conditions {

		if InjectionTestor.MatchString(condition.Field) {
			logging.Warn("发现SQL注入：%v", condition)
			return nil, errors.New("发现SQL注入：" + fmt.Sprintf("%v", condition))
		}

		var conf interface{}
		if len(condition.Operate) == 0 {
			condition.Operate = "="
		}

		switch condition.Operate {
		case "like":
			//session.And(condition.Field+" LIKE ?", "%"+condition.Value.(string)+"%")
			conf = builder.Like{condition.Field, condition.Value.(string)}
		case "=":
			fs := builder.Eq{}
			fs[condition.Field] = condition.Value
			conf = fs
		case ">=":
			fs := builder.Gte{}
			fs[condition.Field] = condition.Value
			conf = fs
			//session.And(condition.Field+" >= ?", condition.Value)
		case ">":
			fs := builder.Gt{}
			fs[condition.Field] = condition.Value
			conf = fs
			//session.And(condition.Field+" > ?", condition.Value)
		case "<=":
			fs := builder.Lte{}
			fs[condition.Field] = condition.Value
			conf = fs
			//session.And(condition.Field+" <= ?", condition.Value)
		case "<":
			fs := builder.Lt{}
			fs[condition.Field] = condition.Value
			conf = fs
			//session.And(condition.Field+" < ?", condition.Value)
		case "nil":
			conf = builder.IsNull{condition.Field}
			//session.And(condition.Field + " IS NULL")
		case "nnil":
			conf = builder.NotNull{condition.Field}
			//session.And(condition.Field + " IS NULL")
		case "!=":
			conf = builder.Neq{condition.Field: condition.Value}
			//session.And(condition.Field+" <> ?", condition.Value)
		case "in":
			o := condition.Value.([]interface{})
			if len(o) > 0 {
				conf = builder.In(condition.Field, o)
				//session.In(condition.Field, o)
			} else {
				conf = builder.Eq{"ID": "NULL"}
				//session.And("1 = 0")
			}
		case "between":
			o := condition.Value.([]interface{})
			//session.And(condition.Field+" >= ? AND "+condition.Field+" <= ?", o[0], o[1])
			conf = builder.Between{Col: condition.Field, LessVal: o[0], MoreVal: o[1]}
		}

		wstr, arg, err := builder.ToSQL(conf)

		if err != nil {
			logging.Warn("条件错误：%v", err)
			return nil, errors.New("条件错误：" + fmt.Sprintf("%v", err))
		}

		*sql += " AND " + wstr + " "
		args = append(args, arg...)
	}
	return args, err
}
